﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports Npgsql
Imports WSEntidades
Imports Utilerias
Imports MySql.Data.MySqlClient
Imports WSFactory

Public Class CatCuentasDAL
    Private cadConex As String = ""

    Public Sub New()
        Me.cadConex = DBConnString.generarCadenaConexion(My.Settings.Server,
                                                         My.Settings.Port,
                                                         My.Settings.DB,
                                                         My.Settings.Un,
                                                         RijndaelSimple.desencriptaDefault(My.Settings.Up))
        'Me.cadConex = "Server=localhost;Port=5432;User Id=postgres;Password=123456;Database=test;Enlist=true"
        'Me.cadConex = "server=localhost;database=bdglobal;uid=root;password=MQ+123456;port=3306;"
    End Sub

#Region "FuncionesUtilizables"
    Public Function existeCuenta(ByVal pRFC As String) As Boolean
        Dim numReg As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM CatCuentas " & _
                                            "WHERE RFC = ?;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("RFC", pRFC)
                    myConn.Open()
                    numReg = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (numReg > 0)
        Catch ex As Exception
            Throw
        Finally
            numReg = Nothing
        End Try
    End Function

    Public Function getCadenaConexionCliente(ByVal pRFC As String, ByVal pStatus As CatCuentasBE.EstatusCuenta) As String
        Dim strCadConexCte As String = ""
        Dim servBE As CatServidoresBE = Nothing
        Dim strNombreBD As String = ""
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT catcuentas.NombreBD, " & _
                                                "catservidores.Host, " & _
                                                "catservidores.Port, " & _
                                                "catservidores.Username, " & _
                                                "catservidores.Password " & _
                                            "FROM catcuentas " & _
                                                "INNER JOIN catservidores " & _
                                                        "ON (catcuentas.IdServidor = catservidores.IdServidor) " & _
                                            "WHERE catcuentas.RFC = ? " & _
                                                    "AND catcuentas.Estatus = ?;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("RFC", pRFC)
                    Select Case pStatus
                        Case CatCuentasBE.EstatusCuenta.Activa
                            myComm.Parameters.AddWithValue("Status", "A")
                        Case CatCuentasBE.EstatusCuenta.Inactiva
                            myComm.Parameters.AddWithValue("Status", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("Status", "A")
                    End Select

                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows) Then
                            myDR.Read()
                            strNombreBD = convertDTFromDb.ConvertString(myDR.Item("NombreBD")).Trim
                            servBE = CatServidoresFact.BuildForConnString(myDR)
                        Else
                            strNombreBD = ""
                            servBE = Nothing
                        End If
                    End Using
                End Using
            End Using

            If (strNombreBD <> "" And servBE IsNot Nothing) Then
                strCadConexCte = DBConnString.generarCadenaConexion(servBE.Host,
                                                                    servBE.Puerto,
                                                                    strNombreBD,
                                                                    servBE.Usuario,
                                                                    servBE.Pass)
            End If

            Return strCadConexCte
        Finally
            strCadConexCte = Nothing
            servBE = Nothing
            strNombreBD = Nothing
        End Try
    End Function

    Public Function registraCatCuentas(ByVal pCatCuentas As CatCuentasBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO CatCuentas(RFC, Nombre, Correo, Telefono,IdServidor, NombreBD, Estatus, FechaRegistro) " & _
                                            "VALUES (@RFC,@Nombre,@Correo,@Telefono,@IdServidor,  @NombreBd,@Estatus, NOW());"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@RFC", pCatCuentas.Rfc)
                    myComm.Parameters.AddWithValue("@Nombre", pCatCuentas.Nombre)
                    myComm.Parameters.AddWithValue("@Correo", pCatCuentas.Correo)
                    myComm.Parameters.AddWithValue("@Telefono", pCatCuentas.Telefono)
                    myComm.Parameters.AddWithValue("@IdServidor", pCatCuentas.IdServidor)
                    myComm.Parameters.AddWithValue("@NombreBd", pCatCuentas.NombreBd)
                    Select Case pCatCuentas.Status
                        Case CatCuentasBE.EstatusCuenta.Activa
                            myComm.Parameters.AddWithValue("@Estatus", "A")
                        Case CatCuentasBE.EstatusCuenta.Inactiva
                            myComm.Parameters.AddWithValue("@Estatus", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Estatus", "I")
                    End Select
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizaCatCuentas(ByVal pEmpresa As empresaBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE catcuentas " & _
                                            "SET Nombre = @nombre, " & _
                                                "Correo = @correo, " & _
                                                "Telefono = @telef " & _
                                            "WHERE RFC = @rfc;"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@Nombre", pEmpresa.RazonSocial)
                    myComm.Parameters.AddWithValue("@Correo", pEmpresa.Correo)
                    myComm.Parameters.AddWithValue("@telef", pEmpresa.Telefono)
                    myComm.Parameters.AddWithValue("@rfc", pEmpresa.RFC)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function


    Public Function creaBDUsuario(ByVal pNombreBD As String, ByVal pRfcEmi As String,
                                  ByVal pRazSocEmi As String, ByVal pCorreo As String,
                                  ByVal pServ As CatServidoresBE, ByRef pPwdUsr As String,
                                  ByVal pTelefono As String) As Boolean
        Dim scriptBDCtes As String = ""
        Dim strCadConexDest As String = ""
        Try
            pPwdUsr = RandomPassword.Generate(8)
            strCadConexDest = DBConnString.generarCadenaConexionParaCrearBD(pServ.Host,
                                                                 pServ.Puerto,
                                                                 pServ.Usuario,
                                                                 pServ.Pass)
            scriptBDCtes = System.IO.File.ReadAllText(System.IO.Path.Combine(My.Settings.PathFiles, "DbScripts\scriptBdClientes.sql"))
            scriptBDCtes = scriptBDCtes.Replace("[DBNAME]", pNombreBD)
            scriptBDCtes = scriptBDCtes.Replace("[RFC_EMI]", pRfcEmi)
            scriptBDCtes = scriptBDCtes.Replace("[PWD]", Utilerias.RijndaelSimple.encriptaDefault(pPwdUsr))
            scriptBDCtes = scriptBDCtes.Replace("[USR_MAIL]", pCorreo)
            scriptBDCtes = scriptBDCtes.Replace("[RAZ_SOC_EMI]", pRazSocEmi)
            scriptBDCtes = scriptBDCtes.Replace("[TELEFONO]", pTelefono)
            Using myConn As MySqlConnection = New MySqlConnection(strCadConexDest)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "CREATE DATABASE " & pNombreBD & ";"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        myComm.CommandText = scriptBDCtes
                        myComm.Parameters.Clear()
                        myComm.ExecuteNonQuery()
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function


#End Region

    Public Function modificaCatCuentas(ByVal pCatCuentas As CatCuentasBE) As Boolean
        Dim instrSql As String
        Dim oInsParm(0 To 7) As OdbcParameter
        Try
            instrSql = "UPDATE ""CatCuentas"" " & _
                                        "SET ""Rfc""=?, ""Nombre""=?, ""Correo""=?, ""Telefono""=?,""IdServidor""=?, ""NombreBd""=?, ""Status""=? " & _
                                                "WHERE ""Id"" = ?;"

            oInsParm(0) = New OdbcParameter("", pCatCuentas.Rfc)
            oInsParm(1) = New OdbcParameter("", pCatCuentas.Nombre)
            oInsParm(2) = New OdbcParameter("", pCatCuentas.Correo)
            oInsParm(3) = New OdbcParameter("", pCatCuentas.Telefono)
            oInsParm(4) = New OdbcParameter("", pCatCuentas.IdServidor)
            oInsParm(5) = New OdbcParameter("", pCatCuentas.NombreBd)
            oInsParm(6) = New OdbcParameter("", pCatCuentas.Status)
            oInsParm(7) = New OdbcParameter("", pCatCuentas.Id)

            'Crea el objeto connection
            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddRange(oInsParm)
                    myConn.Open()
                    myComm.ExecuteNonQuery()
                    If (myComm.ExecuteNonQuery() > 0) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using

        Catch ex As Exception
            Throw
        Finally
            instrSql = Nothing
            oInsParm = Nothing
        End Try
    End Function

    Public Function getCatCuentas(ByVal pNombre As String, ByVal pStrCatCuentas As String) As List(Of CatCuentasBE)
        Dim CatCuentasLs As List(Of CatCuentasBE) = New List(Of CatCuentasBE)
        Dim instrSql As String

        Try
            instrSql = "SELECT ""Rfc"", ""Nombre"", ""Correo"", ""Id"", ""Telefono"",""IdServidor"", ""NombreBd"", ""Status"" " & _
                                     "FROM ""CatCuentas"" " & _
                                    "WHERE ""Nombre"" = ? " & _
                                        "AND ""Status"" = '%" & pStrCatCuentas & "%' ;"

            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As OdbcCommand = New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Add(New OdbcParameter("", pNombre))
                    myConn.Open()
                    Using myDR As OdbcDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            'Dim CatCuentas As CatCuentasBE = CatCuentasFact.Build(myDR)
                            'If (Not CatCuentas Is Nothing) Then
                            '    CatCuentasLs.Add(CatCuentas)
                            'End If
                            'CatCuentas = Nothing
                        End While
                    End Using
                End Using
            End Using
            Return CatCuentasLs
        Catch ex As Exception
            Throw
        Finally
            CatCuentasLs = Nothing
            instrSql = Nothing
        End Try
    End Function

    Public Function getCatCuentasByRFC(ByVal pRFC As String) As CatCuentasBE
        Dim instrSql As String
        Dim tmpRFC As CatCuentasBE
        Try
            instrSql = "SELECT ""Rfc"", ""Nombre"", ""Correo"", ""Id"", ""Telefono"",""IdServidor"", ""NombreBd"", ""Status"" " & _
                                    "FROM ""CatCuentas"" " & _
                                    "WHERE ""Rfc"" = ? ;"

            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Add(New OdbcParameter("", pRFC))
                    myConn.Open()
                    Using myDR As OdbcDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpRFC = Nothing 'CatCuentasFact.Build(myDR)
                        Else
                            tmpRFC = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpRFC

        Catch ex As Exception
            Throw
        Finally
            tmpRFC = Nothing
            instrSql = ""
        End Try
    End Function

End Class
